# -*- coding: utf-8 -*-
"""
Created on Thu Nov 16 12:23:46 2017

@author: leshuangjiang
"""

import os
import sqlite3 as sql
import xlwings as xw
#import time
import pandas as pd

PathSum = (r"D:\文档\表格\IE科\18Q4CFOB梭织面料价格.xlsx")
#def drop_table():
#    drop = '''drop table data'''
#    cur.execute(drop)
#    conn.commit()
#    return '表格已经被删除'    
def create_table():
    ''''''
    create = '''create table data (编号 text, 名称 text, 
                                   成分 text, 规格 text, 
                                   门幅 text, 价格 decimal(3,3));
    '''
    cur.execute(create)
    conn.commit()
#    
def data_get(book):
    ''''''
    sheet = book.sheets(1)
    col = ['编号', '名称', '成分', '规格', '缩率', '克重', '门幅', '工艺', '价格']
    loc_1 = ''.join(['a', '1:', 'a', str(5000)])
    rg = [i[0] for i in list(sheet.Range(loc_1).value) if i[0]!=None]
    loc_2 = ''.join(['a',  '2:', 'i', str(len(rg))])
    all_data = [list(i) for i in list(sheet.Range(loc_2).value)]
    book.close
    df = pd.DataFrame(all_data, columns=col)
    need_df = df.loc[:,['编号', '名称', '成分', '规格','门幅', '价格']]
#    need_df['款号'] = need_df['款号'].astype('str').str[:-2]
    need_df['编号'] = need_df['编号'].astype('str').str[:-2]
    need_df.replace([None], '', inplace=True)
    return need_df.values    
def data_trans():
    ''''''
    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False
    app.screen_updating = False
    xb_fuliao = app.books.api.open(PathSum, 0, True, None, None, None, True)
#    xb_mianliao = app.books.open(r'D:\文档\表格\IE科\面料辅料表\面辅料\副本18Q3梭织面料---ODM --11月10日.xlsx')
    all_d = data_get(xb_fuliao)
    app.quit()
#    all_d.extend(DaTa(xb_mianliao))
    return all_d
def data_in():
    '''写入数据到数据库'''
    for i in data_trans():
        insert = '''insert into data ('编号', '名称', '成分', '规格','门幅', '价格') 
                    values (?, ?, ?, ?, ?, ?)'''
        #
        para = ['' if x is None else x for x in i]
        print(para)
        cur.execute(insert, para)
    conn.commit()
    conn.close()
    return '所有数据已经转换完成'

#col = ['款号', '名称', '成分', '规格', '缩率', '克重', '门幅', '工艺', '价格']

if __name__ == '__main__':
    try:
        os.remove(r'D:\价格批导\MYSQL\cfobAcc.db')
    except:
        print('数据表已经存在')
    finally:
        conn = sql.connect(r'D:\价格批导\MYSQL\cfob.db')
        cur = conn.cursor()
        create_table()        
    print('开始写入数据')
    data_in()
    os.system('taskkill /f /im excel.exe')
#    os.system('taskkill /PID app.pid')
    print('所有的数据都已经写入')